package org.dusin.weijiwufang.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.log4j.Logger;
import org.dusin.weijiwufang.bo.VisitorBean;
import org.dusin.weijiwufang.dao.VisitorDao;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;


/**
 * 在JavaDB数据库上的实现
 * @author dusin
 */
public class VisitorDaoImpl implements VisitorDao {

    static Logger logger = Logger.getLogger(VisitorDaoImpl.class);
    private JdbcTemplate jdbcTemplate;
    public int size;

    public VisitorDaoImpl(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public boolean doCreate(VisitorBean userBean) {
        boolean flag = true;
        String sql = null;
        sql = "INSERT INTO visitor(loginName,password,employeeNo,realName,"
                + "createdTime,level,contact,note)"
                + " VALUES (?,?,?,?,?,?,?,?)";
        jdbcTemplate.update(sql,
                userBean.getLoginName(),
                userBean.getPassword(),
                userBean.getEmployeeNo(),
                userBean.getRealName(),
                userBean.getCreateTime(),
                userBean.getLevel(),
                userBean.getContact(),
                userBean.getNote());
        return flag;
    }

    @Override
    public VisitorBean findById(long id){
        String sql = "SELECT id,loginName,password,employeeNo,realName,level,"
                + "createdTime,contact,note FROM visitor where id=?";
        return this.jdbcTemplate.queryForObject(sql,new Object[]{id}
                ,new VisitorBeanMapper());
    }
    
    /**
     * 用来验证用户登录，返回非size=0表示可以登录
     * 
     * @param loginName
     * @param password
     * @return 
     */
    @Override
    public List<VisitorBean> findByLoginNamePassword(String loginName,String password) {        
        String sql = "SELECT id,loginName,password,employeeNo,realName,level,"
                + "createdTime,contact,note FROM visitor where loginName=? and password=?";
        return this.jdbcTemplate.query( sql, new Object[]{loginName,password},new VisitorBeanMapper());      
    }
    
//    public VisitorBean findByLoginName(String loginName){
//        String sql = "SELECT id,loginName,password,employeeNo,realName,level,"
//                + "createdTime,contact,note FROM visitor where loginName=?";
//        return this.jdbcTemplate.queryForObject(sql,new Object[]{loginName}
//                ,new VisitorBeanMapper());
//    }
    
    /**
     * 用来判断是否有同名用户，查找某个用户名的个数
     * @param loginName
     * @return 
     */
    @Override
    public int findForIntByLoginName(String loginName){
        String sql = "SELECT count(*) FROM visitor where loginName=?";
        return this.jdbcTemplate.queryForInt( sql, loginName);      
    }
    
    @Override
    public List<VisitorBean> findAll() {        
        String sql = "SELECT id,loginName,password,employeeNo,realName,level,"
                + "createdTime,contact,note FROM visitor";
        return this.jdbcTemplate.query( sql, new VisitorBeanMapper());      
    }

    @Override
    public boolean doUpdate(VisitorBean visitor) {
        String sql = "update visitor set loginName=?,password=?,employeeNo=?"
                + ",realName=?,level=?, contact=?,note=? where id = ?";
        this.jdbcTemplate.update(sql,visitor.getLoginName()
                ,visitor.getPassword(),visitor.getEmployeeNo(),visitor.getRealName()
                ,visitor.getLevel() ,visitor.getContact(),visitor.getNote()
                ,visitor.getId());
        return true;       
    }

    @Override
    public boolean doDeleteById(long id) {
        String sql  = "delete from visitor where id=?";
        this.jdbcTemplate.update(sql,id);
        return true;
    }
    
    
    
    private static final class VisitorBeanMapper implements RowMapper<VisitorBean> {
        
        @Override
        public VisitorBean mapRow(ResultSet rs, int rowNum) throws SQLException {
            VisitorBean visitor = new VisitorBean();            
            visitor.setId(rs.getLong("id"));
            visitor.setLoginName(rs.getString("loginName"));
            visitor.setPassword(rs.getString("password"));
            visitor.setEmployeeNo(rs.getString("employeeNo"));
            visitor.setLevel(rs.getByte("level"));
            visitor.setNote(rs.getString("note"));
            visitor.setContact(rs.getString("contact"));
            visitor.setCreateTime(rs.getTimestamp("createdTime"));
            return visitor;
        }
    }
    
    
}
